﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
/// <summary>
///DBHelp 的摘要说明
/// </summary>
public class DBHelp
{
    private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["scon"].ConnectionString;
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="sname"></param>
    /// <param name="sp"></param>
    /// <param name="para"></param>
    /// <returns></returns>
    public static string ExcuteStore(string sname, SqlParameter[] sp, string para)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sname, conn))
            {
                comm.CommandType = CommandType.StoredProcedure;
                if (sp != null)
                {
                    comm.Parameters.AddRange(sp);
                }
                comm.ExecuteNonQuery();
                return comm.Parameters[para].Value.ToString();
            }
        }
    }
    /// <summary>
    /// 增删改方法(事务)
    /// </summary>
    /// <param name="sqls"></param>
    /// <returns></returns>
    public static bool NonQuery(List<string> sqls)
    {
        int rs = 0;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            SqlTransaction stran = conn.BeginTransaction();
            using (SqlCommand comm = new SqlCommand())
            {
                comm.Transaction = stran;
                comm.Connection = conn;
                try
                {
                    foreach (string sql in sqls)
                    {
                        rs = 0;
                        comm.CommandText = sql;
                        rs = comm.ExecuteNonQuery();
                    }
                    stran.Commit();
                }
                catch (Exception)
                {

                    stran.Rollback();
                }
            }
        }
        return rs > 0 ? true : false;
    }
    /// <summary>
    /// 增删改
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static bool NonQuery(string sql)
    {
        int rs = 0;
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                rs = comm.ExecuteNonQuery();
            }
        }
        return rs > 0 ? true : false;
    }
    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable Query(string sql)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

